let express = require('express');
let path = require('path');
const https = require('https')
let router = express.Router();

router.get('/', function (req, res) {
    if (!req.session.username) {
        res.redirect('/user/index')
    }
    const io = req.app.get('io')
    const connection = req.app.get('connection')

    // 获取地址
    getLocation(connection, req.session.username)

    io.on('connection', socket => {
        // 用户上线
        socket.join(req.session.username)
        socket.emit('login', req.session.username)
        update(connection, 'volunteer', ['status'], ['online'], `username="${req.session.username}"`)

        // 发送请求列表和在线志愿者
        sendVolunteers(connection, io)
        sendRequests(connection, socket)
        sendFavorites(connection, socket, req.session.username)

        // 用户发起请求
        socket.on('submit-request', request => {
            let content = request.content
            let kind = request.kind
            let receiver = request.receiver
            let datetime = new Date().toISOString().slice(0, 19).replace('T', ' ')
            connection.query(`select lat, lon, place from user where username='${req.session.username}'`, (err, location) => {
                if (err) {
                    console.log(err)
                }
                else {
                    let requestInfo = {
                        sender: req.session.username,
                        receiver: receiver,
                        content: content,
                        time: datetime,
                        kind: kind,
                        state: 'idle',
                        lat: location[0].lat,
                        lon: location[0].lon,
                        place: location[0].place
                    }
                    // 请求入库
                    let keys = ['sender', 'receiver', 'content', 'time', 'kind', 'state', 'lat', 'lon', 'place']
                    insert(connection, 'help', keys, requestInfo)
                    assignRequest(connection, receiver, io)
                }
                sendRequests(connection, io)
            })


        })

        // 完成请求
        socket.on('finish-request', h_id => {
            connection.query(`select * from help where h_id=${h_id}`, (err, result) => {
                if (err) {
                    console.log(err)
                }
                else {
                    console.log(result[0])
                    io.to(result[0].sender).emit('grade', h_id)
                }
            })
        })

        socket.on('disconnect', () => {
            console.log(req.session.username + ' disconnect')
            update(connection, 'volunteer', ['status'], ['offline'], `username="${req.session.username}"`)
        })
    })
    console.log(__dirname)
    res.sendFile(path.join(__dirname, '../public/map/map.html'));
})

function sendVolunteers(connection, socket) {
    connection.query('select * from volunteer where status != "offline"', (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            socket.emit('show-volunteers', result)
        }
    })
}

function sendRequests(connection, socket) {
    connection.query('select * from help where state = "idle"', (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            socket.emit('show-requests', result)
        }
    })
}

function sendFavorites(connection, socket, username) {
    connection.query(`select * from favorite where user='${username}'`, (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            socket.emit('show-favorites', result)
        }
    })
}

function getLocation(connection, username) {
    let minLat = 28.1576
    let maxLat = 28.1732
    let minLon = 112.9206
    let maxLon = 112.9342
    let lon = Math.random() * (maxLon - minLon) + minLon
    let lat = Math.random() * (maxLat - minLat) + minLat

    update(connection, 'user', ['lat', 'lon'], [lat, lon], `username="${username}"`)
    update(connection, 'volunteer', ['lat', 'lon'], [lat, lon], `username="${username}"`)
    update(connection, 'user', ['place'], ['湖南 长沙 中南大学'], `username="${username}"`)
    update(connection, 'volunteer', ['place'], ['湖南 长沙 中南大学'], `username="${username}"`)
}

// 分配请求给志愿者
function assignRequest(connection, receiver, io) {
    // 获取请求
    connection.query('select * from campus_help.help order by h_id desc limit 1', (err, request) => {
        if (err) {
            console.log(err)
        }
        else {
            if (receiver) {
                io.to(receiver).emit('assign-request', {request: request[0], kind: 'specified'})
            }
            else {
                // 获取在线的志愿者
                connection.query('select * from volunteer where status="online" limit 1', (err, volunteers) => {
                    if (err) {
                        console.log(err)
                    }
                    else {
                        io.to(volunteers[0].username).emit('assign-request', {request: request[0], kind: 'all'})
                    }
                })
            }
        }
    })
}

router.get('/search', function (req, res) {
    let connection = req.app.get('connection')
    let content = req.query.content;
    console.log('搜索: ' + content);
    let sql = "select * from city where city='"+content+"'";
    connection.query(sql, function(err, result) {
        if (err) throw err;
        if (result.length != 0) {
            console.log("搜索成功");
            res.send({
                status: 'place',
                minLon: result[0].minLon,
                maxLon: result[0].maxLon,
                minLat: result[0].minLat,
                maxLat: result[0].maxLat
            });
        } else {
            console.log("未查询到地点");
            connection.query(`select *
                              from volunteer
                              where username = "${content}"`, (err, result) => {
                if (err) {
                    console.log(err)
                }
                if (result.length != 0) {
                    console.log('搜索到志愿者')
                    res.send({
                        status: 'volunteer',
                        volunteer: result[0]
                    })
                }
                else {
                    res.send({
                        status: 'unsuccessful'
                    })
                }

            })
        }
    })
})

router.get('/getRequest', (req, res) => {
    const connection = req.app.get('connection')
    connection.query(`select * from help where h_id = ${req.query.h_id}`, (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            res.send(result[0])
        }
    })
})

router.get('/getPath', (req, res) => {
    const connection = req.app.get('connection')
    update(connection, 'volunteer', ['status'], ['busy'], `username='${req.session.username}'`)
    update(connection, 'help', ['state', 'receiver'], ['assigned', `${req.session.username}`], `h_id=${req.query.h_id}`)
    insert(connection, 'assignedRequest', ['h_id'], {h_id: req.query.h_id})
    let sql = `select help.lat destLat, help.lon destLon, volunteer.lat originLat, volunteer.lon originLon from help, volunteer where help.h_id=${req.query.h_id} and volunteer.username='${req.session.username}';`
    connection.query(sql, (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            res.send(result[0])
        }
    })
})

router.get('/getVolunteer', (req, res) => {
    const connection = req.app.get('connection')
    connection.query(`select * from volunteer where username = '${req.query.username}'`, (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            res.send(result[0])
        }
    })
})

router.get('/getFirstAssigned', (req, res) => {
    const connection = req.app.get('connection')
    connection.query(`select * from assignedRequest limit 1;`, (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            connection.query('delete from assignedRequest limit 1;')
            res.send(result[0])
        }
    })
})

router.get('/finishRequest', (req, res) => {
    const connection = req.app.get('connection')
    const io = req.app.get('io')
    update(connection, 'help', ['state'], ['finished'], `h_id=${req.query.h_id}`)
    update(connection, 'volunteer', ['status'], ['online'], `username='${req.session.username}'`)
    sendRequests(connection, io)
})

router.get('/getAllVolunteers', (req, res) => {
    const connection = req.app.get('connection')
    connection.query(`select lat, lon from volunteer where status='online';`, (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            res.send(result)
        }
    })
})

// 用户提交举报，存入数据库
router.get('/submitReport', (req, res) => {
    const connection = req.app.get('connection')
    let report = {
        reported: req.query.reported,
        reporter: req.session.username,
        datetime: new Date().toISOString().slice(0, 19).replace('T', ' '),
        content: req.query.content
    }
    insert(connection, 'report', ['reported', 'reporter', 'time', 'content'], report)
})

// 收藏或取消收藏志愿者
router.get('/addToFavorite', (req, res) => {
    const connection = req.app.get('connection')
    let favorite = {
        user: req.session.username,
        love: req.query.username
    }
    let type = req.query.type
    let love = req.query.username
    let user = req.session.username
    if (type == 'add') {
        insert(connection, 'favorite', ['user', 'love'], favorite)
    }
    else {
        _delete(connection, 'favorite', `user='${user}' and love='${love}'`)
    }
})

function update(connection, table, keys, values, where) {
    for (let i = 0; i < keys.length; i++) {
        let sql = `update ${table} set ${keys[i]} = "${values[i]}" where ${where}`
        connection.query(sql, (err, result) => {
            if (err) {
                console.log(err)
            }
            else {
                console.log(sql + ' 执行成功')
            }
        })
    }
}

function insert(connection, table, keys, values) {
    let columns = '('
    let vals = '('
    for (let i = 0; i < keys.length; i++) {
        columns += keys[i]
        columns += ','
        vals = vals + '?,'
    }
    columns = columns.slice(0, columns.length - 1) + ')'
    vals = vals.slice(0, vals.length - 1) + ')'
    let sql = `insert into ${table} ${columns} values ${vals};`
    connection.query(sql, Object.values(values), (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            console.log(sql + '执行成功')
        }
    })
}

function _delete(connection, table, where) {
    let sql = `delete from ${table} where ${where}`
    connection.query(sql, (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            console.log(sql + ' 执行成功')
        }
    })
}

module.exports = router;